# -*- coding: utf-8 -*-
import typing
import uuid

from sqlalchemy import Column, String, Text, Integer, DateTime, select, update, BigInteger, Index, JSON
from sqlalchemy.orm import aliased

from app.models.base import Base
from app.schemas.hrms.hrms_schemas import DepartmentSchemas, DutySchemas


class Department(Base):
    """组织表"""
    __tablename__ = 'department'

    name = Column(String(64), nullable=False, comment='组织名称', index=True)
    type = Column(String(64), nullable=False, comment='组织类型')
    department_route_id = Column(String(128), nullable=False, comment='组织id路由')
    department_route = Column(String(512), nullable=False, comment='组织路由')
    parent_id = Column(Integer, nullable=False, comment='上级组织id')
    parent_name = Column(String(64), nullable=False, comment='上级组织名称')
    remarks = Column(String(255), nullable=False, comment='用户描述')

    @classmethod
    async def get_list(cls, params: DepartmentSchemas):
        q = [cls.enabled_flag == 1]
        if params.name:
            q.append(cls.name.like('%{}%'.format(params.name)))
        if params.department_route:
            q.append(cls.department_route.like('%{}%'.format(params.department_route)))
        stmt = select(*cls.get_table_columns()) \
            .where(*q) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_department_route_by_name(cls, params):
        # 根据名称查询子级组织
        q = [cls.enabled_flag == 1]
        if params['name']:
            q.append(cls.department_route.like('%{}%'.format(params['name'])))
        stmt = select(*cls.get_table_columns()) \
            .where(*q) \
            .order_by(cls.id.desc())
        return await cls.get_result(stmt)

    @classmethod
    async def get_department_route_id(cls, department_route_id: str):
        # 根据名称查询子级组织
        if not department_route_id:
            return []
        q = [cls.enabled_flag == 1, cls.department_route_id != department_route_id, cls.department_route_id != None]
        if department_route_id:
            q.append(cls.department_route_id.like('{}%'.format(department_route_id)))
        stmt = select(*cls.get_table_columns()).where(*q)
        return await cls.get_result(stmt)

    @classmethod
    async def get_department_by_id(cls, params: DepartmentSchemas):
        """获取组织id"""
        stmt = select(cls.get_table_columns()).where(cls.id == params.id, cls.enabled_flag == 1)
        return await cls.get_result(stmt)

    @classmethod
    async def get_department_by_parent_id(cls, parent_id: int):
        """获取组织id"""
        stmt = select(cls.get_table_columns()).where(cls.id == parent_id, cls.enabled_flag == 1)
        return await cls.get_result(stmt, first=True)

    @classmethod
    async def get_department_by_name(cls, name: str):
        stmt = select(cls.get_table_columns()).where(cls.name == name, cls.enabled_flag == 1)
        return await cls.get_result(stmt, True)

    @classmethod
    async def get_department_list(cls, params: DepartmentSchemas):
        """查询组织列表"""
        q = [cls.enabled_flag == 1]
        if params.name:
            q.append(cls.name.like('%{}%'.format(params.name)))
        stmt = select(*cls.get_table_columns()) \
            .where(*q) \
            .order_by(cls.updation_date.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_department_all(cls):
        """查询组织列表"""
        d = aliased(Department)
        stmt = (select(cls.get_table_columns(), d.name.label("new_parent_name")).where(cls.enabled_flag == 1)
                .outerjoin(d, d.id == cls.parent_id)
                .order_by(cls.updation_date))

        return await cls.get_result(stmt)


class Duty(Base):
    """岗位表"""
    __tablename__ = 'duty'

    name = Column(String(64), nullable=False, comment='岗位名称', index=True)
    type = Column(String(64), nullable=False, comment='岗位类型')
    level = Column(String(64), nullable=False, comment='岗位等级')
    remarks = Column(String(255), nullable=False, comment='备注')

    @classmethod
    async def get_list(cls, params: DutySchemas):
        q = [cls.enabled_flag == 1]
        if params.name:
            q.append(cls.name.like('%{}%'.format(params.name)))
        stmt = select(*cls.get_table_columns()) \
            .where(*q) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_duty_by_id(cls, params: DutySchemas):
        """获取岗位id"""
        stmt = select(cls.get_table_columns()).where(cls.id == params.id, cls.enabled_flag == 1)
        return await cls.get_result(stmt)
